SharePoint Learning Kit Customization
SLK+MLC Schema

This section introduces the concept of LearningStore schema, and describes the schema used by SharePoint Learning Kit and the underlying Microsoft Learning Components.

LearningStore Schemas, Item Types, Views

The SLK Queries and Query Sets section introduced the concept of LearningStore view—a "virtual table" of data with built-in security rules. LearningStore views are part of a larger concept, LearningStore schema, which is a definition of the kind of data that can be managed by Microsoft Learning Components for an e-learning application such as SLK.

In addition to views, a LearningStore schema can contain LearningStore item types, which correspond directly to tables in the database—"real" tables, as opposed to views which are "virtual" tables.

MLC defines a base schema, containing basic item types and views required for executing e-learning content. The base schema is defined by an XML file, BaseSchema.xml. Applications based on MLC have the option of extending the base schema using another XML file which can extend item types, add new item types, and add new views, among other things. SLK, which is an application based on MLC, extends the base schema to add concepts such as "assignment" and "learner assignment". SLK's extensions to the MLC base schema are defined in SlkSchema.xml.

Item types and views can define database operation-level security rules, implemented as SQL expressions which evaluate to 1 if the operation is allowed to execute and 0 if it isn't. (Items support the operations "query", "add", "delete", and "update"; views only support "query".) Since views are implemented as SQL queries, views can also define row-level security rules that determine which rows get returned, based in part on the role(s) of the current user.

Note  The term "row-level security" is sometimes used in other technologies to refer to a broader authorization concept. In LearningStore, "row-level security" simply means a set of conditions that are added to the SQL query of a view—in effect, reducing the set of rows returned from a query to those that the current user is permitted to see. Note that LearningStore also provides mechanisms for securing adds, updates, and deletes of rows, but those fall under the category of "operation-level security". Also, note that operation-level security rules typically make use of information within rows of the table—unless the rule is simply "grant access to all users" or "grant access to no users".

Consider the following examples of a SLK item type compared to a SLK view.

**By the way, If you clicked on links above, such as InstructorAssignmentItem and InstructorAssignmentListForInstructors, you may notice that there are classes, accessible from C# or other programming languages, corresponding to LearningStore item types and views. These classes are generated automatically by the MLC schema compiler (the tool that converts schema XML such as SlkSchema.xml, into .sql and .cs files), and they exist primarily for programming convenience. For example, rather than referring to a column named "AssignmentStartDate", you can use the string constant InstructorAssignmentListForInstructors.AssignmentStartDate, which is longer but is type-checked by the compiler, so you'll catch typing mistakes at compile time. The documentation for these classes also provides further information about these item types and views.

In addition to helper classes such as InstructorAssignmentItem, the schema compiler generates a LearningStore item identifier class for each item type. For example, the item identifier class corresponding to InstructorAssignmentItem is InstructorAssignmentItemIdentifier. This is a class which represents, in a type-safe way, a numeric identifier for a row in the InstructorAssignmentItem table.

Both item types and views can be queried. You can use an item type name (e.g. "UserItem", or, for better compile-time type checking, UserItem.ItemTypeName) in a query displayed in Assignment List Web Part or in a LearningStore query, just like you can use a view name (e.g. "UserWebList" or UserWebList.ViewName)—provided that sufficient operation-level security permissions have been granted (see example).

The next few subsections below describe the MLC and SLK schemas. The last subsection below explains how to customize SLK views and create new views, which allow you to add to the functionality of the SLK Assignment List Web Part and to perform additional querying operations within applications that access SLK programmatically.

Note  The Schema column below indicates which schema (MLC base schema or SLK extensions to MLC) define the item type or view. When the Schema column contains MLC+, that's an indication of an item type which is defined in the MLC base schema and extended (with additional columns, in this case) in the SLK schema.

Note  The Access Granted To column below defines the conditions under which the current user (i.e. the browser user logged into SharePoint) can access the item type or view using Assignment List Web Part or LearningStore queries. When applications access these items and views using the MLC API or SLK API, or using LearningStorePrivilegedScope, different rules sometimes apply (as described in the API documentation, e.g. SlkStore). For example, by default the current user cannot access the AttemptItem table, but of course MLC can access the AttemptItem table on the user's behalf when MLC executes e-learning content. Also, note that Access Granted To column specifies operation-level security rules, but views may specify additional row-level security rules. For example, even if "Access Granted To" specifies "All users", for views it's possible that the row-level security rules mean you'll get zero rows returned from a query.

Item Types & Views Related To Users
Name Type Schema Each Row Contains Access Granted To
UserItem Item MLC+ Information about one user, including a numeric identifier (UserItemIdentifier) used elsewhere in the database to refer to the user. UserItem items contain a Key property (database column) which, for SLK, contains either the security ID (SID) of the user or the user's login name. (The login name is used for authentication mechanisms such as forms-based authentication which don't use security IDs.) MLC: no users; SLK: query access for all users.
UserWebListItem Item SLK One entry in one user's user Web list, for one site collection. A user Web list is the list of Web sites that appears on the E-Learning Actions page for all document libraries within a given site collection. Each user has at most one user Web list for each site collection (SPSite). No users.
UserWebList View SLK One entry in one user's user Web list, for one site collection. Like UserWebListItem, but the UserWebList view is restricted to user Web lists owened by the current user. All users.
LearnerGlobalObjectiveItem Item MLC Information about the progress of one learner against a particular global objective, across all activities in all e-learning packages. These objectives may be global to a single package or global to the system—the data in this table does not distinguish between these two cases. No users.
Item Types Related To E-Learning Packages

These item types (tables) contain information about e-learning packages. This information is not specific to any assignment or attempt on the package. These tables are not involved in assignments of non-e-learning documents.

Name Type Schema Each Row Contains Access Granted To
PackageItem Item MLC+ Information about one e-learning package. Before a package can be attempted, a row referring to it must be added to this table. When that happens, detailed information about the package is added to other tables in the schema; for example, information about each activity is added to the ActivityPackageItem table. No users.
ActivityPackageItem Item MLC Information about one activity within one e-learning package. No users.
ResourceItem Item MLC Information about resources used in activities, including the XML of each <resource> element in the package. No users.
ActivityObjectiveItem Item MLC Information about one local objective of one activity within one e-learning package. Objectives that are added to a package by a SCO via the RTE are not included in this table. No users.
CommentFromLmsItem Item MLC A "comments from LMS" string related to a specific activity within an e-learning package. These are read-only by SCOs. SLK does not add rows to the CommentsFromLmsItem table. No users.
GlobalObjectiveItem Item MLC Information about one global objective related to one organization of one e-learning package, or one global objective that's not constrained to any one organization of any package. No users.
MapActivityObjectiveTo­GlobalObjectiveItem Item MLC A mapping between an ActivityObjectiveItem and a GlobalObjectiveItem. No users.
Item Types & Views Related to Assignments

SLK's LearningStore schema adds the concept of assignments to MLC's base schema. The following item types and views help implement this and related concepts.

Name Type Schema Each Row Contains Access Granted To
AssignmentItem Item SLK Information about one assignment. No users.
AssignmentPropertiesView View SLK Information about one assignment, as well as information about the e-learning package (if any) associated with the assignment. This view returns information about a single assignment, specified as a view parameter. Instructors and learners of the assignment.
AssignmentListForInstructors View SLK Information about one assignment, as well as information about the e-learning package (if any) associated with the assignment. This view returns one row for each assignment for which the current user is an instructor. All users.
InstructorAssignmentItem Item SLK Information about one instructor assignment, i.e. the mapping of one instructor to one assignment. For example, if an assignment has three instructors, there will be three InstructorAssignmentItem table rows associated with that assignment. No users.
InstructorAssignmentListForInstructors View SLK Information about one instructor assignment, as well as information about the e-learning package (if any) associated with the assignment. This view returns one row for each instructor on each assignment for which the current user is an instructor. All users.
LearnerAssignmentItem Item SLK Information about one learner assignment, i.e. the mapping of one learner to one assignment. For example, if an assignment has 30 learners, there will be 30 LearnerAssignmentItem table rows associated with that assignment. No users.
LearnerAssignmentListForInstructors View SLK Information about one learner assignment, as well as information about the e-learning package (if any) associated with the assignment. This view returns one row for each learner on each assignment for which the current user is an instructor. All users.
LearnerAssignmentListForLearners View SLK Information about one learner assignment, as well as information about the e-learning package (if any) associated with the assignment. This view returns one row for each assignment for which the current user is a learner. All users.
LearnerAssignmentView View SLK Specific information about a learner assignment. No users.
Items Types Related to Attempts

The following item types help implement attempts on e-learning packages. These tables are not involved in assignments of non-e-learning documents.

Name Type Schema Each Row Contains Access Granted To
AttemptItem Item MLC+ Information about one attempt, i.e. one instance of one learner executing one organization of one e-learning package. If the same learner attempts the same organization twice, two AttemptItem items (table rows) are created. No users.
PackageGlobalObjectiveItem Item MLC Information about progress on one global objective related to one attempt. No users.
ActivityAttemptItem Item MLC Information about one activity in the context of one attempt. No users.
AttemptObjectiveItem Item MLC Information about the learner's progress on one local objective for one activity of one attempt. No users.
CommentFromLearnerItem Item MLC One comment from the learner related to one activity of one attempt. No users.
SequencingLogEntryItem Item MLC One sequencing log entry, related to one activity of one attempt, or related to an entire attempt. See StoredLearningSession.LoggingOptions. No users.
ExtensionDataItem Item MLC One "extension" data model element. An "extension" data model element is a data model element whose name does not begin with "cmi." or "adl.". No users.
InteractionItem Item MLC Information about one interaction within one activity of one attempt. No users.
RubricItem Item MLC Information about a rubric that is attached to an interaction. No users.
EvaluationCommentItem Item MLC A comment from an evaluator (i.e. an instructor grading a learner assignment) related to one InteractionItem. Only used for e-learning packages in Class Server format. No users.
CorrectResponseItem Item MLC Information about one SCORM correct response string for one InteractionItem. The CorrectResponseItem table contains one row in this table per correct response per interaction. No users.
InteractionObjectiveItem Item MLC Relates one InteractionItem with one AttemptObjectiveItem. No users.
Customing and Creating New Views and Security Rules

The SLK Queries and Query Sets section gave a brief example (the "Users" query) of how the LearningStore schema of a SLK database can be upgraded to add a security rule. Here, we explain in more detail how to add and update views and security rules.

For More Information
SLK Concepts
SLK Settings
Default SLK Settings XML
SLK Queries and Query Sets
SLK API Overview
LearningStore Overview (MLC)
LearningStore Schema Reference (MLC)
SlkSettings Class (SLK API)
Next Topic: SLK Settings